/**
 * MySQL 数据库配置
 * EndoSight-UC 医疗AI系统
 *
 * 使用MySQL数据库替代SQLite，提供更好的并发性能和数据完整性
 */

import mysql from 'mysql2/promise';
import dotenv from 'dotenv';
import { createSecureLogger } from '../utils/secureLoggingConfig.js';

// 确保环境变量被加载
dotenv.config({ override: true });

const logger = createSecureLogger({
  service: 'MySQLDB',
  logLevel: process.env.LOG_LEVEL || 'info'
});

// 数据库连接池配置
const poolConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT) || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'endo_sight_uc',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  charset: 'utf8mb4',
  timezone: '+08:00'
};

// 数据库连接池
let pool = null;

// 性能监控统计
const performanceStats = {
  totalQueries: 0,
  slowQueries: 0,
  averageQueryTime: 0,
  queryTimes: [],
  errorCount: 0,
  lastReset: new Date()
};

// 初始化MySQL连接池
const initializeDatabase = async () => {
  try {
    logger.info('正在初始化MySQL数据库连接...');

    // 创建连接池
    pool = mysql.createPool(poolConfig);

    // 测试连接
    const connection = await pool.getConnection();
    await connection.ping();
    connection.release();

    logger.info('✅ MySQL数据库连接成功');
    logger.info(`📍 数据库: ${poolConfig.host}:${poolConfig.port}/${poolConfig.database}`);

    // 初始化数据库表结构
    await initializeTables();

    logger.info('🎉 MySQL数据库初始化完成');
    return true;

  } catch (error) {
    logger.error('❌ MySQL数据库初始化失败:', error.message);
    throw error;
  }
};

// 初始化数据库表结构
const initializeTables = async () => {
  const tables = [
    // 医生表
    `CREATE TABLE IF NOT EXISTS doctors (
      doctor_id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(50) UNIQUE NOT NULL,
      password_hash VARCHAR(255) NOT NULL,
      salt VARCHAR(255) NOT NULL,
      real_name VARCHAR(100) NOT NULL,
      department VARCHAR(100),
      title VARCHAR(100),
      role TINYINT DEFAULT 1 COMMENT '1:普通医生 2:主治医生 3:主任',
      status TINYINT DEFAULT 1 COMMENT '0:禁用 1:启用',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX idx_username (username),
      INDEX idx_status (status),
      INDEX idx_role (role)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,

    // 患者表
    `CREATE TABLE IF NOT EXISTS patients (
      patient_id INT AUTO_INCREMENT PRIMARY KEY,
      patient_code VARCHAR(50) UNIQUE NOT NULL,
      gender TINYINT DEFAULT 0 COMMENT '0:未知 1:男 2:女',
      birthday DATE,
      phone VARCHAR(20),
      address VARCHAR(500),
      notes TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX idx_patient_code (patient_code),
      INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,

    // 评分任务表
    `CREATE TABLE IF NOT EXISTS scoring_tasks (
      task_id INT AUTO_INCREMENT PRIMARY KEY,
      patient_id INT,
      doctor_id INT NOT NULL,
      status TINYINT DEFAULT 1 COMMENT '1:待处理 2:处理中 3:已完成 4:已取消',
      submit_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      complete_time TIMESTAMP NULL,
      image_url VARCHAR(500),
      clinical_text TEXT,
      ai_inference_status TINYINT DEFAULT 0 COMMENT '0:未开始 1:进行中 2:已完成',
      ai_inference_time TIMESTAMP NULL,
      ai_error_message TEXT,
      FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE SET NULL,
      FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
      INDEX idx_doctor_id (doctor_id),
      INDEX idx_patient_id (patient_id),
      INDEX idx_status (status),
      INDEX idx_submit_time (submit_time),
      INDEX idx_status_doctor (status, doctor_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,

    // 评分结果表
    `CREATE TABLE IF NOT EXISTS scoring_results (
      result_id INT AUTO_INCREMENT PRIMARY KEY,
      task_id INT UNIQUE NOT NULL,
      score DECIMAL(3,1) CHECK (score >= 0 AND score <= 8),
      severity ENUM('缓解期', '轻度', '中度', '重度'),
      confidence DECIMAL(5,4) CHECK (confidence >= 0 AND confidence <= 1),
      model_version VARCHAR(50),
      analysis_details TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (task_id) REFERENCES scoring_tasks(task_id) ON DELETE CASCADE,
      INDEX idx_task_id (task_id),
      INDEX idx_severity (severity),
      INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,

    // 系统日志表
    `CREATE TABLE IF NOT EXISTS system_logs (
      log_id INT AUTO_INCREMENT PRIMARY KEY,
      level ENUM('DEBUG', 'INFO', 'WARN', 'ERROR') NOT NULL,
      message TEXT NOT NULL,
      data JSON,
      timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      source VARCHAR(100),
      INDEX idx_timestamp (timestamp),
      INDEX idx_level (level)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
  ];

  for (const sql of tables) {
    try {
      await pool.execute(sql);
      logger.info('✅ 表结构创建/验证成功');
    } catch (error) {
      logger.error('❌ 表结构创建失败:', error.message);
      throw error;
    }
  }

  // 创建默认管理员用户
  await createDefaultAdmin();
};

// 创建默认管理员用户
const createDefaultAdmin = async () => {
  try {
    const bcrypt = await import('bcryptjs');
    const salt = await bcrypt.genSalt(10);
    const passwordHash = await bcrypt.hash('admin123', salt);

    const sql = `INSERT IGNORE INTO doctors (username, password_hash, salt, real_name, role, status)
                 VALUES (?, ?, ?, ?, ?, ?)`;

    await pool.execute(sql, ['admin', passwordHash, salt, '系统管理员', 3, 1]);
    logger.info('✅ 默认管理员用户创建成功');

  } catch (error) {
    logger.warn('创建默认管理员用户失败:', error.message);
  }
};

// 测试连接
export const testConnection = async () => {
  if (!pool) {
    await initializeDatabase();
  }

  try {
    const connection = await pool.getConnection();
    await connection.ping();
    connection.release();
    logger.info('✅ MySQL连接测试成功');
    return true;
  } catch (error) {
    logger.error('❌ MySQL连接测试失败:', error.message);
    throw error;
  }
};

// 执行查询
export const query = async (sql, params = []) => {
  if (!pool) {
    await initializeDatabase();
  }

  const startTime = Date.now();

  try {
    logger.debug(`执行SQL: ${sql}`);
    if (params.length > 0) {
      logger.debug(`参数:`, params);
    }

    const [rows] = await pool.execute(sql, params);
    const queryTime = Date.now() - startTime;

    updatePerformanceStats(sql, queryTime, true);
    logger.debug(`查询完成: ${rows.length} 条记录，耗时: ${queryTime}ms`);

    return rows;

  } catch (error) {
    const queryTime = Date.now() - startTime;
    updatePerformanceStats(sql, queryTime, false);

    logger.error('数据库查询失败:', {
      error: error.message,
      code: error.code,
      sql: sql,
      params: params
    });

    throw error;
  }
};

// 执行函数（与query相同）
export const execute = query;

// 事务函数
export const transaction = async (callback) => {
  if (!pool) {
    await initializeDatabase();
  }

  const connection = await pool.getConnection();

  try {
    await connection.beginTransaction();

    const conn = {
      query: async (sql, params) => {
        const [rows] = await connection.execute(sql, params);
        return rows;
      },

      execute: async (sql, params) => {
        const [rows] = await connection.execute(sql, params);
        return [rows, []]; // MySQL2兼容格式
      }
    };

    const result = await callback(conn);
    await connection.commit();

    connection.release();
    return result;

  } catch (error) {
    await connection.rollback();
    connection.release();
    throw error;
  }
};

// 数据库健康检查
export const healthCheck = async () => {
  try {
    const startTime = Date.now();
    await testConnection();
    const responseTime = Date.now() - startTime;

    // 获取数据库统计信息
    const stats = await query(`
      SELECT
        'doctors' as table_name, COUNT(*) as count
      FROM doctors
      UNION ALL
      SELECT 'patients' as table_name, COUNT(*) as count
      FROM patients
      UNION ALL
      SELECT 'scoring_tasks' as table_name, COUNT(*) as count
      FROM scoring_tasks
      UNION ALL
      SELECT 'scoring_results' as table_name, COUNT(*) as count
      FROM scoring_results
    `);

    return {
      status: 'healthy',
      timestamp: new Date().toISOString(),
      response_time_ms: responseTime,
      database_type: 'MySQL',
      database_host: `${poolConfig.host}:${poolConfig.port}`,
      database_name: poolConfig.database,
      statistics: stats.reduce((acc, row) => {
        acc[row.table_name] = row.count;
        return acc;
      }, {})
    };
  } catch (error) {
    return {
      status: 'unhealthy',
      timestamp: new Date().toISOString(),
      error: error.message,
      database_type: 'MySQL'
    };
  }
};

// 初始化数据库
export const initialize = async () => {
  try {
    await initializeDatabase();
    logger.info('🎉 MySQL数据库完全初始化完成');
    return true;
  } catch (error) {
    logger.error('❌ MySQL数据库初始化失败:', error);
    throw error;
  }
};

// 确保数据库在使用前初始化
export const ensureInitialized = async () => {
  if (!pool) {
    await initialize();
  }
};

// 更新性能统计
const updatePerformanceStats = (sql, queryTime, success) => {
  performanceStats.totalQueries++;

  if (!success) {
    performanceStats.errorCount++;
  }

  performanceStats.queryTimes.push(queryTime);

  // 只保留最近1000次查询的时间记录
  if (performanceStats.queryTimes.length > 1000) {
    performanceStats.queryTimes = performanceStats.queryTimes.slice(-1000);
  }

  // 检查是否为慢查询
  if (queryTime > 1000) {
    performanceStats.slowQueries++;
    logger.warn(`🐌 慢查询检测:`, {
      sql: sql.substring(0, 100) + (sql.length > 100 ? '...' : ''),
      queryTime: `${queryTime}ms`
    });
  }

  // 计算平均查询时间
  const sum = performanceStats.queryTimes.reduce((a, b) => a + b, 0);
  performanceStats.averageQueryTime = Math.round(sum / performanceStats.queryTimes.length);
};

// 获取性能统计信息
export const getPerformanceStats = () => {
  const uptime = Date.now() - performanceStats.lastReset.getTime();

  return {
    ...performanceStats,
    uptime_ms: uptime,
    slowQueryRate: performanceStats.totalQueries > 0
      ? Math.round((performanceStats.slowQueries / performanceStats.totalQueries) * 100 * 100) / 100
      : 0,
    errorRate: performanceStats.totalQueries > 0
      ? Math.round((performanceStats.errorCount / performanceStats.totalQueries) * 100 * 100) / 100
      : 0,
    qps: uptime > 0 ? Math.round((performanceStats.totalQueries / uptime) * 1000) : 0
  };
};

// 重置性能统计
export const resetPerformanceStats = () => {
  performanceStats.totalQueries = 0;
  performanceStats.slowQueries = 0;
  performanceStats.averageQueryTime = 0;
  performanceStats.queryTimes = [];
  performanceStats.errorCount = 0;
  performanceStats.lastReset = new Date();
  logger.info('📊 性能统计已重置');
};

// 关闭连接池
export const closeDatabase = async () => {
  if (pool) {
    await pool.end();
    pool = null;
    logger.info('✅ MySQL连接池已关闭');
  }
};

// 优雅关闭处理
process.on('SIGINT', async () => {
  await closeDatabase();
  process.exit(0);
});

process.on('SIGTERM', async () => {
  await closeDatabase();
  process.exit(0);
});

export default {
  query,
  execute,
  testConnection,
  transaction,
  healthCheck,
  initialize,
  ensureInitialized,
  closeDatabase,
  getPerformanceStats,
  resetPerformanceStats
};